## -*- coding: UTF-8 -*-
"""
    This module represents collection manager.
"""
from __future__ import with_statement
import gettext
import threading
import traceback
import gobject
import time
import os
import sys
from pysqlite2 import dbapi2 as sqlite
from . import version
from . import exceptions
from . import log
from . import settings
from . import database
from . import path
from . import strunicode as su
from .plugin import get_plugin
from .plugin import find_plugin_for_file
from .plugin import construct_attr as ca

db = None

def check_database(cursor=None):
    if cursor is None:
        db = database.get_connection()
        cursor = db.cursor()
    
    query = "SELECT setting_value FROM settings WHERE setting_name='version'"
    try:
        database.execute(query, cursor=cursor)
    except exceptions.DbError:
        raise exceptions.CollectionError(_("Unable to access database to check version! "
                                           "Most probably database is broken."))
    
    res = cursor.fetchone()
    
    if res is None:
        raise exceptions.CollectionError(_("Unknown database version!"))
    
    if str(res[0]) != "1":
        raise exceptions.CollectionError(_("Not supported database version: %s!") % res[0])
    
def process_one_file(fpath, file_id, mtime, plugin_id, cursor):
    """
    Extract information from the file and put it in database.
    
    @param fpath: full path to file that should be processed
    @param file_id: book file id in the database, corresponding record MUST present in 
        the database at the moment of function call.
    @param mtime: file modification time
    @param plugin_id: plugin id
    @param cursor: db connection cursor
    """
    plugin = get_plugin(plugin_id)
    # try to read new metadata
    try:
        metadata = plugin.extract_metadata(fpath)
    except:
        # delete book from the database
        delete_book(file_id, cursor=cursor)
        raise

    query = "UPDATE files SET mtime=? WHERE file_id=?"
    database.execute(query, args=(mtime, file_id), cursor=cursor)
    
    # delete existing metadata
    queries = (
        "DELETE FROM metadata WHERE file_id=? AND attr_name IN ('title', 'genre', 'author', 'sequence')",
        "DELETE FROM metadata_params WHERE file_id=?",
        )
    for query in queries:
        database.execute(query, args=(file_id,), cursor=cursor)

    for m in metadata:
        
        # m['attr_name'], m['attr_value'], m['attr_params']
        query = "INSERT INTO metadata (file_id, attr_name, attr_value) VALUES (?, ?, ?)"
        database.execute(query, args=(file_id, m['attr_name'], m['attr_value']), cursor=cursor)
        metadata_element_id = database.last_insert_id(cursor)
        
        if isinstance(m['attr_params'], list) and len(m['attr_params']) != 0:
            argslist = [ [file_id, metadata_element_id]+param for param in m['attr_params'] ]
                
            query = "INSERT INTO metadata_params (file_id, metadata_element_id, param_name, param_value) VALUES (?, ?, ?, ?)"
            database.executemany(query, argslist=argslist, cursor=cursor)

def format_seconds(n):
    """
    Convert seconds value to human readable form, 12->"12 seconds", 120->"2 minutes" etc
    """
    sec = n % 60
    n /= 60
    min = n % 60
    hour = n / 60

    res = ""
    if hour != 0:
        res += gettext.ngettext("%d hour ", "%d hours ", hour) % hour

    if min != 0:
        res += gettext.ngettext("%d minute ", "%d minutes ", min) % min

    if sec != 0:
        res += gettext.ngettext("%d second", "%d seconds", sec) % sec

    return res

class ThreadStopRequested(exceptions.PbsException):
    pass

class ScanThread(threading.Thread):
    """
    Class that implements directory scanning routine, also it fills database with found data.
    
    Some kind of Singleton.
    """

    running = False
    stopped = False

    def __init__(self, database, progress_func, done_func):
        """
        Initializes the thread
        
        @param database: database connection
        @param progress_func: a Python function or method object, thread method run() call
            this function to report scanning progress. Must have the following signature:
                
                >>> def callback(progress_value, progress_text)
                
            progress_value is a float number between 0 and 1
        @param done_func: a Python function or method object, thread method run() call
            this function when finish execution. Must have the following signature:
                
                >>> def callback(report_file_name)
        """
        # call threading.Thread contsructor
        super(self.__class__, self).__init__(name=self.__class__.__name__)
        self.db = database
        self.progress_func = progress_func
        self.done_func = done_func
        self.rep = None
        #self.progress = 0

    def run(self):
        """
        Method representing the thread's activity.
        """
        self.report_filename = os.path.join(path.reports_dir, time.strftime("update-%Y%m%d-%H%M-%S.log"))
        self.rep = log.get_file_logger(self.report_filename)
        self.rep.info("Collection update log started. PyBookshelf version: %s." % version.__version__)
        if ScanThread.running:
            log.debug("ScanThread is already running")
            return
        ScanThread.running = True
        ScanThread.stopped = False
        log.debug("Starting ScanThread")

        collection_dirs = settings.get_list("collection/dirs")
        self.found_files = [] # list of all found files
        
        gobject.idle_add(self.progress_func, 0.01, _("Scanning collection directories"))

        for dir in collection_dirs:
            if not self.process_path(dir): 
                return

        files_count = len(self.found_files)
        self.rep.info("Total %s files found." % files_count)

        # get database connection
        db = database.get_connection()
        cur = db.cursor()
        self.rep.info("Drop temporary tables before scan.")
        query = "DROP TABLE IF EXISTS  _files"
        database.execute(query, cursor=cur)
        self.rep.info("Create temporary tables.");
        query = """CREATE TEMPORARY TABLE _files (file_id INTEGER, path VARCHAR(4096), mtime INT NOT NULL,
            update_locked INT default 0, plugin VARCHAR(255), PRIMARY KEY(file_id))"""
        database.execute(query, cursor=cur)

        gobject.idle_add(self.progress_func, 0.02, _("Updating database"))

        # get genres map, this map will be updated during thread run
        # and db should be updated when thread finish
        #genres_map = get_genres_map(cursor=cur)

        # fill temporary table with all found files
        for file, plugin_id in self.found_files:
            if self.stop_requested():
                return
            mtime = os.path.getmtime(file)
            query = "INSERT INTO _files (file_id, path, mtime, plugin) VALUES (NULL, ?, ?, ?)"
            database.execute(query, cursor=cur, args=(buffer(file), mtime, plugin_id))
        db.commit()

        # find files that have to be deleted, files have to be updated, and files have to be added
        # first find new files: select all files that doesn't present in the table `files'
        query = '''
            SELECT _files.path, _files.mtime, _files.plugin  
            FROM _files LEFT JOIN files 
            ON _files.path=files.path 
            WHERE files.file_id IS NULL'''
        database.execute(query, cursor=cur)
        new_files = []
        map(new_files.append, cur)
        #for row in cur:
        #    new_files.append(row)
        self.rep.info("%d new files have been found" % len(new_files))

        # now let's find updated files, i.e. those one having files.mtime less than
        # _files.mtime
        query = '''
            SELECT files.file_id, files.path, _files.mtime, _files.plugin, files.update_locked, "align-string" 
            FROM files JOIN _files 
            ON files.path=_files.path AND files.mtime<_files.mtime'''
        # "align-string" is required because tuple row must contain four elements
        database.execute(query, cursor=cur)
        updated_files = []
        updated_files_sizes = []
        for row in cur:
            updated_files.append(row)
            updated_files_sizes.append(os.path.getsize(row[1]))
        self.rep.info("%d files have been modified so will be updated in the db" % len(updated_files))

        # finally, find deleted files
        query = '''
            SELECT files.file_id 
            FROM files LEFT JOIN _files 
            ON files.path=_files.path
            WHERE _files.file_id IS NULL'''
        database.execute(query, cursor=cur)
        deleted_files = [row[0] for row in cur]
        self.rep.info("%d files were removed from filesystem so will be deleted from db too" % len(deleted_files))

        # now process all lists: new_files, updated_files, deleted_files
        try:
            # we are using python 2.5 new statement `with', in this case we 
            # don't have to execute commit()/rollback() methods because they 
            # will be called automatically (rollback() on exception,
            # commit on successful exit from "with" statement)
            with db:
                # first remove deleted files from all affected tables
                self.rep.info("Remove from database files deleted from filesystem, %s records" % len(deleted_files))
                if len(deleted_files):
                    queries = (
                        "DELETE FROM files WHERE file_id=?",
                        "DELETE FROM metadata WHERE file_id=?",
                        "DELETE FROM metadata_params WHERE file_id=?",
                        )
                    for query in queries:
                        if self.stop_requested():
                            raise Exception("Stop requested")
                        database.executemany(query, argslist=[(id,) for id in deleted_files], cursor=cur)
                #queries = ("DELETE FROM files WHERE id=?", "DELETE FROM books WHERE file_id=?")
                #for id in deleted_files:
                #    for query in queries:
                #        database.execute(query, args=(id,), cursor=cur)

                # now insert new records into the table `files'
                self.rep.info("Insert new files into the database, %s files" % len(new_files))

                # insert new records into the table `files'
                for fpath, mtime, plugin_id in new_files:
                    if self.stop_requested():
                        raise Exception("Stop requested")
                    query = "INSERT INTO files (file_id, path, mtime, plugin) VALUES (NULL, ?, ?, ?)"
                    database.execute(query, args=(fpath, mtime, plugin_id), cursor=cur)
                    # append new record into updated_files (as it existed there before)
                    id = database.last_insert_id(cur)
                    updated_files.append((id, fpath, mtime, plugin_id, False, True)) 
                    # ^^^ False means that file is not locked for update
                    # True means that file is new
                    updated_files_sizes.append(os.path.getsize(str(fpath)))

                # list containing ids of broken books, they must be removed from table `files'
                broken_books = []
                
                # list containing new broken files
                new_broken_books = []

                self.rep.info("Update secondary tables, %s files" % len(updated_files))
                # now update secondary tables
                time_start = time.time()
                time_total = 0.0
                total_files = len(updated_files)
                processed_files = 0
                total_bytes = 0
                processed_bytes = 0
                for x in updated_files_sizes:
                    total_bytes += x
                it = iter(updated_files_sizes)
                
                for id, fpath, mtime, plugin_id, update_locked, is_new_file in updated_files:
                    if self.stop_requested():
                        raise ThreadStopRequested("")
                    
                    processed_bytes += it.next()
                    fpath = str(fpath) #su.unicode2str(fpath)
                    self.rep.info("Processing file %d of %d `%s' " % (processed_files+1, total_files, fpath) )

                    if update_locked:
                        # don't process locked files
                        self.rep.info("File is locked, skip.")
                        continue
                    
                    try:
                        process_one_file(fpath, id, mtime, plugin_id, cur)
                        self.rep.info("OK")
                        
                    except IOError, e:
                        self.rep.error("Unable to process file `%s': %s" % (fpath, str(e)))
                        log.error("Unable to process file `%s':" % (fpath, ), exc_info=1)
                    except exceptions.BookFormatError, e:
                        if is_new_file == True:
                            self.rep.error("Unable to process new file `%s': %s" % (fpath, str(e)))
                            log.error("Unable to process new file `%s':" % (fpath, ), exc_info=1)
                            new_broken_books.append(id)
                        else:
                            self.rep.error("Unable to process file existing in the db `%s': %s" % (fpath, str(e)))
                            log.error("Unable to process file existing in the db `%s':" % (fpath, ), exc_info=1)
                            broken_books.append(id)

                    processed_files += 1
                    ## 0.03, 0.97 because this stage must start with 3% and end with 100%
                    fraction = (0.97 * processed_bytes) / total_bytes + 0.03
                    
                    # calculate average file process time
                    time_total = time.time() - time_start
                    time_per_file = time_total / processed_files
                    
                    # calculate ETA
                    if processed_bytes == 0:
                        processed_bytes = total_bytes
                        
                    #log.debug("%d / %d" % (total_bytes, processed_bytes))
                    time_eta = (time_total / processed_bytes) * total_bytes
                    time_eta2 = time_per_file * total_files
                    #time_eta = (time_eta1 + time_eta2) / 2

                    hr_seconds = format_seconds(int(time_eta - time_total + 0.5))
                    gobject.idle_add(self.progress_func, fraction, _("Processing file %(cur_file)i of %(total_files)i, "
                        "%(time_left)s left") % {
                            'cur_file': processed_files, 
                            'total_files': total_files, 
                            'time_left': hr_seconds}) 
                    # 0.5 because we want to compensate time bla-bla-bla
                
                self.rep.info("Delete records for broken files from database, %d records" % len(broken_books))
                self.rep.info("Delete records for new files whose are broken, %d records" % len(new_broken_books))
                if len(broken_books) or len(new_broken_books):
                    query = "DELETE FROM files WHERE file_id=?"
                    database.executemany(query, 
                        argslist=[(id,) for id in broken_books + new_broken_books], cursor=cur)
        
        except ThreadStopRequested:
            self.rep.info("Scanning process was terminated by request.")
            
        except Exception, e:
            # FIXME add GUI notification on error
            self.rep.info("Unable to commit changes into the database")
            traceback.print_exc()
        
        self.rep.info("Removing temporary tables")
        query = "DROP TABLE IF EXISTS  _files"
        database.execute(query, cursor=cur)
        self.stop()
        del self.rep

    def stop_requested(self):
        """
        Checks that thread is asked for termination and executes
        method stop()
        
        @return: return True if thread termination is requested
        """
        if ScanThread.stopped:
            log.debug("Stop scanning requested")
            self.stop()
            return True
        return False

    def stop(self):
        """
        Actions required when thread finishes
        """
        log.debug("Stopping ScanThread")
        ScanThread.stopped = False
        ScanThread.running = False
        if self.done_func:
            gobject.idle_add(self.done_func, self.report_filename)
            self.done_func = None
    
    def process_path(self, path):
        """
        Recursively scan one directory path and append to the member self.found_files
        all found files that matches some criteria.
        
        @param path: full path to directory that should be scanned
        @return: return False if thread termination was requested during scan
        """
        log.debug("process path: %s" % path)
        # TODO replace code below with some kind of plugins usage
        # find all files ends with ".fb2" or ".fb2.zip"
        
        for root, dirs, files in os.walk(path):
            for file in files:
                if self.stop_requested():
                    return False
                
                plugin_id = find_plugin_for_file(file)
                if plugin_id is None:
                     self.rep.info("Unable to find appropriate plugin for the file: `%s'" % file)
                     continue
                
                self.found_files.append( (os.path.join(root, file), plugin_id) )
                    
                #if file.lower().endswith(".fb2") \
                #    or file.lower().endswith(".zip") \
                #    or file.lower().endswith(".fb2z"):
                #    self.found_files.append(os.path.join(root, file))
        return True

def initialize(database):
    """
    Module initialization function, must be called before any use of module's functions
    """
    #TODO: allow re-initialization
    global db
    
    if not db is None:
        raise CollectionException("Collection already initialized!")

    db = database

def update_collection(progress_func, done_func):
    """
    Launch collection database update thread
    
    @param progress_func: callback function for reporting progress information
    @param done_func: callback function for reporting collection scan finish
    """
    scan_thread = ScanThread(db, progress_func, done_func)
    scan_thread.start()
    
def update_one_file(file_id, cursor=None):
    """
    Re-read metadata from the file and update database
    
    @param file_id: db record id of the book
    @param cursor: database connection cursor, if omitted default cursor will be used
    """
    if cursor is None:
        db = database.get_connection()
        cursor = db.cursor()

    book_path = get_book_file(file_id, cursor=cursor)
    plugin_id = find_plugin_for_file(book_path)
    with cursor.connection:
        # first delete metadata records related to given file
        query = "DELETE FROM metadata WHERE file_id=?"
        database.execute(query, args=(file_id,), cursor=cursor)
        query = "DELETE FROM metadata_params WHERE file_id=?"
        database.execute(query, args=(file_id,), cursor=cursor)
        
        process_one_file(book_path, file_id, plugin_id, cursor=cursor)
        mtime = os.path.getmtime(book_path)
        query = "UPDATE files SET mtime=? WHERE file_id=?"
        database.execute(query, args=(mtime, file_id), cursor=cursor)

def delete_book(file_id, cursor=None, commit=False):
    """
    Delete all records from db for given book.
    
    @param file_id: db record id of the book
    @param cursor: database connection cursor, if omitted default cursor will be used
    @param commit: if True perform commit to the database
    """
    
    if cursor is None:
        db = database.get_connection()
        cursor = db.cursor()

    queries = (
        "DELETE FROM files WHERE file_id=?",
        "DELETE FROM metadata WHERE file_id=?",
        "DELETE FROM metadata_params WHERE file_id=?"
        )

    for query in queries:
        database.execute(query, args=(file_id,), cursor=cursor)
        
    if commit:
        cursor.get_connection().commit()
    
def cancel_update():
    """
    Terminates collection update and rollback all changes made during database update.
    """
    # TODO: add option to specify what to do with partially 
    # imported data: rollback or commit
    log.debug("Stopping scan thread")
    ScanThread.stopped = True

def get_author_books(author_name, cursor=None):
    """
    Return list of books written by given author
    
    @param author_name: book author name
    @param cursor: database connection cursor, if omitted default cursor will be used
    @return: list of tuples (file_id, book_title), empty list if there are no books for given author
    """
    if cursor is None:
        db = database.get_connection()
        cursor = db.cursor()
    
    query = '''SELECT ma.file_id, mt.attr_value FROM metadata ma
        JOIN metadata mt ON ma.file_id=mt.file_id AND ma.attr_name="author" AND mt.attr_name="title" 
            AND ma.attr_value=?''' 

    database.execute(query, args=(author_name,), cursor=cursor)

    return [row for row in cursor]

def get_book_file(file_id, cursor=None):
    """
    Get path to book file from the db
    
    @param file_id: db record id of the book
    @param cursor: database connection cursor, if omitted default cursor will be used
    @return: full path to book file
    """
    if cursor is None:
        db = database.get_connection()
        cursor = db.cursor()

    query = "SELECT path FROM files WHERE file_id=?"
    database.execute(query, args=(file_id,), cursor=cursor)
    
    res = cursor.fetchone()
    
    if res is None:
        return None
    else:
        return str(res[0])

def is_book_locked(file_id, cursor=None):
    """
    Return book file state
    
    @param file_id: db record id of the book
    @param cursor: database connection cursor, if omitted default cursor will be used
    @return: True if book is locked, False otherwise, None if book with such id doesn't exist
    """
    if cursor is None:
        db = database.get_connection()
        cursor = db.cursor()
        
    query = "SELECT update_locked FROM files WHERE file_id=?"
    database.execute(query, args=(file_id,), cursor=cursor)
    
    res = cursor.fetchone()
    if res is None:
        return None
    
    if res[0] == 1:
        return True
    else:
        return False

def set_book_lock(file_id, update_locked, cursor=None):
    """
    Lock/unlock the book.
    
    @param file_id: db record id of the book
    @param update_locked: True to lock book, False to unlock
    @param cursor: database connection cursor, if omitted default cursor will be used
    """
    if cursor is None:
        db = database.get_connection()
        cursor = db.cursor()
        
    if update_locked:
        update_locked = 1
    else:
        update_locked = 0
        
    query = "UPDATE files SET update_locked=? WHERE file_id=?"
    with cursor.connection:
        database.execute(query, args=(update_locked, file_id), cursor=cursor)
    
    
def get_book_authors(file_id, cursor=None):
    """
    Return list of all authors for given book
    
    @param file_id: db record id of the book
    @param cursor: database connection cursor, if omitted default cursor will be used
    @return: list of book authors or empty list if there are no authors for given book
    """
    if cursor is None:
        db = database.get_connection()
        cursor = db.cursor()
     
    query = "SELECT attr_value FROM metadata WHERE attr_name='author' AND file_id=?"   

    database.execute(query, args=(file_id,), cursor=cursor)
    
    return [row[0] for row in cursor]
    
def get_book_title(file_id, cursor=None):
    """
    Return title of the book
    
    @param file_id: db record id of the book
    @param cursor: database connection cursor, if omitted default cursor will be used
    @return: book title, return None if title not found
    """
    if cursor is None:
        db = database.get_connection()
        cursor = db.cursor()
        
    query = "SELECT attr_value FROM metadata WHERE attr_name='title' AND file_id=?"
    
    database.execute(query, args=(file_id,), cursor=cursor)

    title = None
    
    row = cursor.fetchone()
    if not row is None:
        title = row[0]

    return title
    

def get_series_books(series_name, cursor=None):
    """
    Return list of all books in the given series
    
    @param series_name: name of the series
    @param cursor: database connection cursor, if omitted default cursor will be used
    @return: list of tuples (file_id, number), where number is a book position in the sequence 
    """

    if cursor is None:
        db = database.get_connection()
        cursor = db.cursor()

    # FIXME: add additional JOIN to query to cut off non-existent series
    query = '''SELECT m.file_id, mp.param_value
        FROM metadata m 
            JOIN metadata_params mp ON m.metadata_element_id=mp.metadata_element_id  
                AND attr_name="sequence" AND attr_value=? 
                AND mp.param_name="number"'''
    
    database.execute(query, args=(series_name,), cursor=cursor)
    
    res = [row for row in cursor]

    if res == []:
        res = None

    return res

def get_genres_stat(cursor=None):
    """
    Return genre usage statistics throughout the complete database
    
    @param cursor: database connection cursor, if omitted default cursor will be used
    @return: list of tuples for each genre (genre_name, number_of_books_in_genre)
    """
    if cursor is None:
        db = database.get_connection()
        cursor = db.cursor()
    
    genres_stat = []
    query = "SELECT genre_name, genre_books_number FROM genres_stats_view"
    
    database.execute(query, cursor=cursor)

    genres_stat = [row for row in cursor]
    return genres_stat

def get_genre_authors_stat(genre_name, cursor=None):
    """
    Return statistics information about authors and books for given genre. Each 
        returned tuple contains information about author and how much books
        written by this author database contains  
    
    @param genre_name: name of the genre
    @param cursor: database connection cursor, if omitted default cursor will be used
    @return: list of tuples (author_name, number_of_books_of_author)
    """
    if cursor is None:
        db = database.get_connection()
        cursor = db.cursor()
    query = "SELECT author_name,  COUNT(file_id) FROM genre_authors_view WHERE genre_name=? GROUP BY author_name;"
    
    database.execute(query, args=(genre_name,), cursor=cursor)

    return [row for row in cursor]

def get_genre_author_books(genre_name, author_name, cursor=None):
    """
    Return list of all books for given author and genre
    
    @param genre_name: name of the genre
    @param author_name: name of the author
    @param cursor: database connection cursor, if omitted default cursor will be used
    @return: list of file_id 
    """
    if cursor is None:
        db = database.get_connection()
        cursor = db.cursor()
    query = "SELECT file_id FROM genre_authors_view WHERE author_name=? AND genre_name=?" 
    
    database.execute(query, args=(author_name, genre_name), cursor=cursor)

    res = [row[0] for row in cursor]

    return res

def get_book_metadata(file_id, cursor=None):
    """
    Generate metadata dict from db
    """
    if cursor is None:
        db = database.get_connection()
        cursor = db.cursor()
    
    query = "SELECT metadata_element_id, attr_name, attr_value FROM metadata WHERE file_id=?"
    
    database.execute(query, args=(file_id, ), cursor=cursor)
    
    metadata = []
    rows = [row for row in cursor]
    for metadata_element_id, attr_name, attr_value in rows:
        query = "SELECT param_name, param_value FROM metadata_params WHERE file_id=? AND metadata_element_id=?"
        database.execute(query, args=(file_id, metadata_element_id), cursor=cursor)
        m = ca(attr_name, attr_value, params=[row for row in cursor])
        m['metadata_element_id'] = metadata_element_id
        metadata.append(m)
        
    return metadata
    
def set_book_metadata(file_id, metadata, mark_file_locked=True, cursor=None):
    """
    Update information about book in the database
    
    @param file_id: db record id of the book
    @param metadata: metadata set
    @param mark_file_locked: if True then mark file as locked
    @param cursor: database connection cursor, if omitted default cursor will be used
    """
    if cursor is None:
        db = database.get_connection()
        cursor = db.cursor()
    
    with cursor.connection:
        # first delete all metadata and metadata params
        query = "DELETE FROM metadata WHERE file_id=?"
        database.execute(query, args=(file_id, ), cursor=cursor)
        
        query = "DELETE FROM metadata_params WHERE file_id=?"
        database.execute(query, args=(file_id, ), cursor=cursor)
        
        for m in metadata:
            query = "INSERT INTO metadata (file_id, attr_name, attr_value) VALUES (?, ?, ?)"
            database.execute(query, (file_id, m['attr_name'], m['attr_value']), cursor)
            metadata_element_id = database.last_insert_id(cursor)
            
            if len(m['attr_params']) > 0:
                query = "INSERT INTO metadata_params (file_id, metadata_element_id, param_name, param_value) VALUES (?, ?, ?, ?)"
                database.executemany(query, argslist=[ (file_id, metadata_element_id, param_name, param_value) 
                    for param_name, param_value in m['attr_params'] ], cursor=cursor)
            
        query = "UPDATE files SET update_locked=? WHERE file_id=?"
        if mark_file_locked:
            update_locked = 1
        else:
            update_locked = 0
        database.execute(query, args=(update_locked, file_id), cursor=cursor)
    
def get_metadata_item_matches(attr_name, pattern, cursor=None):
    """
    Perform metadata search
    
    @return: list with found strings
    """
    if cursor is None:
        db = database.get_connection()
        cursor = db.cursor()

    query = "SELECT DISTINCT attr_value FROM metadata WHERE attr_name=? AND isubstr(?, attr_value)"
    database.execute(query, args=(attr_name, pattern), cursor=cursor)
    
    return [row[0] for row in cursor]

def get_general_stats(cursor=None):
    """
    Return dict object with general database statistics.
    
    @param cursor: database connection cursor, if omitted default cursor will be used
    @return: dict object with keys: 'total_books', 'total_authors', 'total_genres'
    """
    if cursor is None:
        db = database.get_connection()
        cursor = db.cursor()

    res = dict()

    query = "SELECT COUNT(*) FROM files"
    database.execute(query, cursor=cursor)
    res['total_books'] = cursor.fetchone()[0]

    query = 'SELECT COUNT(DISTINCT attr_value) FROM metadata WHERE attr_name="author"'
    database.execute(query, cursor=cursor)
    res['total_authors'] = cursor.fetchone()[0]
    
    query = 'SELECT COUNT(DISTINCT attr_value) FROM metadata WHERE attr_name="genre"'
    database.execute(query, cursor=cursor)
    res['total_genres'] = cursor.fetchone()[0]
    
    return res

def get_book_hash(file_id, cursor=None):
    """
    Form value that identifies given book.
    """
    if cursor is None:
        db = database.get_connection()
        cursor = db.cursor()
        
    authors = sorted([author_name.upper() for author_name in get_book_authors(file_id)])
    title = get_book_title(file_id).upper()
    return "%s %s" % (title, "!".join(authors))
    
def get_book_rating(file_id, cursor=None):
    """
    Gets book rating
    
    @param file_id: db record id of the book
    @param cursor: database connection cursor, if omitted default cursor will be used    
    @return: rating value - integer in range [0..5] inclusive
    """
    if cursor is None:
        db = database.get_connection()
        cursor = db.cursor()
        
    book_hash = get_book_hash(file_id, cursor=cursor)
    
    query = "SELECT rating FROM ratings WHERE book_hash=?"
    database.execute(query, args=(book_hash,), cursor=cursor)
    row = cursor.fetchone()
    if row is None:
        return 0
    
    rating = row[0]
    try:
        rating = int(rating)
    except:
        rating = 0 
        
    if rating < 1 or rating > 5:
        rating = 0 

    if rating == 0:
        with cursor.connection:
            query = "DELETE FROM ratings WHERE book_hash=?"
            database.execute(query, args=(book_hash,), cursor=cursor)
        
    return rating
    
def set_book_rating(file_id, rating, cursor=None):
    """
    Sets the book rating. Rating is a value betweeen 0 and 5 (inclusive). 0 means book is not rated
    
    @param file_id: db record id of the book
    @param rating: book rating, integer value, rating must be on of 0,1,2,3,4,5. 
        0 means that book is not rated
    @param cursor: database connection cursor, if omitted default cursor will be used
    """
    if cursor is None:
        db = database.get_connection()
        cursor = db.cursor()
        
    book_hash = get_book_hash(file_id, cursor=cursor)

    try:
        rating = int(rating)
    except:
        rating = 0

    if rating < 1 or rating > 5:
        rating = 0

    if rating == 0:
        with cursor.connection:
            query = "DELETE FROM ratings WHERE book_hash=?"
            database.execute(query, args=(book_hash,), cursor=cursor)
    else:
        with cursor.connection:
            query = "INSERT OR REPLACE INTO ratings (book_hash, rating) VALUES (?, ?)"
            database.execute(query, args=(book_hash, rating), cursor=cursor)

def get_book_shelves(cursor=None):
    """
    Retrieve list of all book shelves.
    
    @param cursor: database connection cursor, if omitted default cursor will be used
    @return: list of tuples (shelf_name, shelf_description)
    """
    if cursor is None:
        db = database.get_connection()
        cursor = db.cursor()

    query = "SELECT attr_value FROM metadata WHERE attr_name='shelf' GROUP BY attr_value"
    
    database.execute(query, cursor=cursor)
    shelves = dict()
    for row in cursor:
        shelves[row[0]] = ""
        
    query = "SELECT shelf_name, shelf_description FROM shelves"
    database.execute(query, cursor=cursor)
    for row in cursor:
        shelves[row[0]] = row[1]
        
    return [(key, value) for key, value in shelves.iteritems()]

def get_book_shelf_books(shelf, cursor=None):
    """
    Retrieve all books for given sehlf 
    
    @param cursor: database connection cursor, if omitted default cursor will be used
    @return: list of book ids
    """
    if cursor is None:
        db = database.get_connection()
        cursor = db.cursor()

    query = "SELECT file_id FROM metadata WHERE attr_name='shelf' AND attr_value=? GROUP BY file_id"
    database.execute(query, args=(shelf,), cursor=cursor)
    
    res = [row[0] for row in cursor]
    return res
    
def get_book_shelf_description(shelf, cursor=None):
    """
    Get the book shelf description.
    
    @params shelf: book shelf name
    @param cursor: database connection cursor, if omitted default cursor will be used
    @return: book shelf description, None if there is no corresponding record in the database
    """
    if cursor is None:
        db = database.get_connection()
        cursor = db.cursor()
        
    query = "SELECT shelf_description FROM shelves WHERE shelf_name=?"
    database.execute(query, args=(shelf,), cursor=cursor)
    
    row = cursor.fetchone()
    
    if row is None:
        return None
    else:
        return row[0]
    
def set_book_shelf_description(shelf, description, cursor=None):
    """
    Sets the book shelf description.
    
    @params shelf: book shelf name
    @param description: new description
    @param cursor: database connection cursor, if omitted default cursor will be used
    """
    if cursor is None:
        db = database.get_connection()
        cursor = db.cursor()
        
    with cursor.connection:
        query = "REPLACE INTO shelves VALUES (?, ?)"
        database.execute(query, args=(shelf, description), cursor=cursor)
    
def rename_book_shelf(shelf, new_shelf, cursor=None):
    """
    Renames the book shelf.
    
    @params shelf: book shelf name
    @param new_shelf: new shelf name
    @param cursor: database connection cursor, if omitted default cursor will be used
    @return: list of book ids
    """
    if cursor is None:
        db = database.get_connection()
        cursor = db.cursor()
        
    # first find if there is shelf with the desired name
    b = get_book_shelf_books(new_shelf)
    d = get_book_shelf_description(new_shelf)

    if len(b) > 0 or d is not None:
        raise exceptions.CollectionError(_("Book shelf with the name “%s” already exist!") % new_shelf)
    
    with cursor.connection:
        query = "UPDATE metadata SET attr_value=? WHERE attr_name='shelf' AND attr_value=?"
        database.execute(query, args=(new_shelf, shelf), cursor=cursor)
         
def delete_book_shelf(shelf, cursor=None, commit=False):
    """
    Delete the bookshelf. Remove all affected metadata.
    
    @params shelf: book shelf name
    @param cursor: database connection cursor, if omitted default cursor will be used
    @param commit: if True perform commit to the database
    @return: list of book ids
    """
    
    if cursor is None:
        db = database.get_connection()
        cursor = db.cursor()
        
    with cursor.connection:
        query = "DELETE FROM shelves WHERE shelf_name=?"
        database.execute(query, args=(shelf,), cursor=cursor)
        
        query = "DELETE FROM metadata WheRE attr_name='shelf' AND attr_value=?"
        database.execute(query, args=(shelf,), cursor=cursor)
        
    if commit:
        db.commit()
